[dbo].[asi_UpdateCampaignResponses]

Property	Value
ANSI Nulls On
Quoted Identifier On

Name	Data Type	Max Length (Bytes)
@useImplicit	bit	1
@userKey	uniqueidentifier	16
@debugging	bit	1

SQL Script

CREATE           PROCEDURE [dbo].[asi_UpdateCampaignResponses]
@useImplicit    bit,
@userKey     uniqueidentifier,
@debugging    bit = 0  --False
AS

-- call the CS stored proc
exec asi_CsUpdateCampaignResponses @useImplicit,@userKey
--The following values were used during testing
--SET @useImplicit = 1
--SET @userKey = '73493EB2-7F2E-4345-A800-0BF5582B16BC'
-- EO 09-21-06 Commenting the rest out, since
-- there's no need for it in Medina
/*
DECLARE @firstSalesHistory    datetime
DECLARE @lastSalesHistory    datetime
DECLARE @newGuid        uniqueidentifier
DECLARE @now             datetime
DECLARE @systemConfigParamName    varchar(50)

SET @systemConfigParamName = 'UpdateCampaignResponsesIsRunning'

SET XACT_ABORT ON -- Any runtime error will result in rollback of transaction

BEGIN TRANSACTION

-- Allow other instances of this sproc to see SystemConfig parameter while this instance is running.
-- This prevents deadlock due to this SystemConfig parameter.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- If necessary, setup the SystemConfig setting to indicate whether this procedure is currently running
IF NOT EXISTS(SELECT 1 FROM SystemConfig WHERE ParameterName = @systemConfigParamName)
   BEGIN
    SET @newGuid = newid()
    SET @now = getdate()

    INSERT SystemConfig (SystemConfigKey, ParameterName, ParameterValue, [Description],
           OrganizationKey, SystemEntityKey, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    VALUES(    @newGuid,
        @systemConfigParamName,
        'True',
        'Indicates whether the asi_UpdateCampaignResponses stored procedure is running.',
        '00000000-0000-0000-0000-000000000000',
        NULL,
        @userKey,
        @now,
        @userKey,
        @now)
   END
ELSE  -- SystemConfig param already exists
   BEGIN
      -- Check if this sproc is already running and, if so, return an error.
      IF EXISTS(SELECT 1
                  FROM SystemConfig
                 WHERE ParameterName = @systemConfigParamName
                   AND ParameterValue = 'True')
         BEGIN
            RAISERROR('Stored procedure asi_UpdateCampaignResponses is already running', 1, 1) WITH SETERROR
            RETURN @@ERROR
         END
      ELSE  -- This sproc is not already running
         BEGIN
        -- Set the SystemConfig param to indicate this sproc is now running
            UPDATE SystemConfig
               SET ParameterValue = 'True'
             WHERE ParameterName = @systemConfigParamName
         END
   END

SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- The default

--Get the last SalesHistory row used in a response update and update the number
-- to the current max
EXECUTE asi_GetSalesHistoryResponseStart @userKey, @firstSalesHistory OUTPUT,
    @lastSalesHistory OUTPUT

SET @now = getdate()

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#tmpResponse'))
   DROP TABLE #tmpResponse

CREATE TABLE #tmpResponse(
    InvoiceNumber        nvarchar(50),
    InvoiceLineNumber    int,
    OrderDate        datetime,
    UserKey            uniqueidentifier,
    ProductKey        uniqueidentifier,
    SourceCode        nvarchar(40),
    TotalRevenue        decimal(18,4),
    WasSolicited        bit NOT NULL DEFAULT 1)

IF @debugging = 1
BEGIN
PRINT 'SalesHistory records where SC is not null'
SELECT sh.InvoiceNumber, sh.InvoiceLineNumber, sh.OrderDate,
       sh.BillToContactKey, sh.ProductKey, sc.Code, sh.ExtendedPrice
  FROM SalesHistory sh
INNER JOIN SourceCode sc ON sh.SourceCodeKey = sc.SourceCodeKey
WHERE sh.CreatedOn > @firstSalesHistory AND sh.CreatedOn <= @lastSalesHistory
   AND sh.SourceCodeKey IS NOT NULL
   AND sc.SourceCodeStatusCode = 0 -- i.e. Is Active
END  --IF @debugging

--SalesHistory records with matching source code
INSERT #tmpResponse(InvoiceNumber, InvoiceLineNumber, OrderDate,
       UserKey, ProductKey, SourceCode, TotalRevenue)
SELECT sh.InvoiceNumber, sh.InvoiceLineNumber, sh.OrderDate,
       sh.BillToContactKey, sh.ProductKey, sc.Code, sh.ExtendedPrice
  FROM SalesHistory sh
INNER JOIN SourceCode sc ON sh.SourceCodeKey = sc.SourceCodeKey
WHERE sh.CreatedOn > @firstSalesHistory AND sh.CreatedOn <= @lastSalesHistory
   AND sh.SourceCodeKey IS NOT NULL
   AND sc.SourceCodeStatusCode = 0 -- i.e. Is Active

IF @debugging = 1
BEGIN
PRINT '#tmpResponse after insert of above'
SELECT * FROM #tmpResponse
END  --IF @debugging

IF @useImplicit = 1
BEGIN

IF @debugging = 1
BEGIN
PRINT 'SalesHistoryRecords where SC is null and relates to appeal'
SELECT DISTINCT sh.InvoiceNumber, sh.InvoiceLineNumber, sh.OrderDate,
       sh.BillToContactKey, sh.ProductKey, sh.ExtendedPrice
  FROM SalesHistory sh
INNER JOIN AppealProduct ap ON sh.ProductKey = ap.ProductKey
INNER JOIN AppealMain am ON ap.AppealKey = am.AppealKey
WHERE sh.CreatedOn > @firstSalesHistory AND sh.CreatedOn <= @lastSalesHistory
   AND sh.SourceCodeKey IS NULL
   AND am.AppealStatusCode = 0 -- 0 is Active
END  --IF @debugging

-- SalesHistory records with matching products and no explicit source code
INSERT #tmpResponse(InvoiceNumber, InvoiceLineNumber, OrderDate,
       UserKey, ProductKey, TotalRevenue)
SELECT DISTINCT sh.InvoiceNumber, sh.InvoiceLineNumber, sh.OrderDate,
       sh.BillToContactKey, sh.ProductKey, sh.ExtendedPrice
  FROM SalesHistory sh
INNER JOIN AppealProduct ap ON sh.ProductKey = ap.ProductKey
INNER JOIN AppealMain am ON ap.AppealKey = am.AppealKey
WHERE sh.CreatedOn > @firstSalesHistory AND sh.CreatedOn <= @lastSalesHistory
   AND sh.SourceCodeKey IS NULL
   AND am.AppealStatusCode = 0 -- 0 is Active

IF @debugging = 1
BEGIN
PRINT '#temResponse after insert of above'
SELECT * FROM #tmpResponse
END  --IF @debugging

--Attempt to find a match using the product and user key
UPDATE tr
   SET SourceCode =
       (SELECT TOP 1 sc.Code
          FROM AppealProduct ap
         INNER JOIN SolicitationMain s ON ap.AppealKey = s.AppealKey
         INNER JOIN SourceCode sc ON s.SolicitationKey = sc.SolicitationKey
         WHERE ap.ProductKey = tr.ProductKey
           AND EXISTS( SELECT 1
                       FROM ListItem
                       WHERE ListKey = sc.SourceCodeKey
                         AND ObjectKey = tr.UserKey)
                     AND sc.LastDropDate =
                             (SELECT MAX(sc2.LastDropDate)
                        FROM AppealProduct ap2
                       INNER JOIN SolicitationMain s2 ON ap2.AppealKey = s2.AppealKey
                       INNER JOIN SourceCode sc2 ON s2.SolicitationKey = sc2.SolicitationKey
                       WHERE ap2.ProductKey = tr.ProductKey))
  FROM #tmpResponse tr
WHERE tr.SourceCode IS NULL

--Attempt to find a match just using the product
UPDATE tr
   SET SourceCode =
       (SELECT TOP 1 sc.Code
       FROM AppealProduct ap
     INNER JOIN SolicitationMain s ON ap.AppealKey = s.AppealKey
     INNER JOIN SourceCode sc ON s.SolicitationKey = sc.SolicitationKey
     WHERE ap.ProductKey = tr.ProductKey
           AND sc.LastDropDate = (SELECT MAX(sc2.LastDropDate)
                            FROM AppealProduct ap2
                           INNER JOIN SolicitationMain s2 ON ap2.AppealKey = s2.AppealKey
                           INNER JOIN SourceCode sc2 ON s2.SolicitationKey = sc2.SolicitationKey
                           WHERE ap2.ProductKey = tr.ProductKey)),
       WasSolicited = 0
  FROM #tmpResponse tr
WHERE tr.SourceCode IS NULL

IF @debugging = 1
BEGIN
PRINT '#tmpResponse before delete of recs w/o source code'
SELECT * FROM #tmpResponse
END  --IF @debugging

--Remove records without a source code
DELETE #tmpResponse
WHERE SourceCode IS NULL

IF @debugging = 1
BEGIN
PRINT '#tmpResponse after delete of recs w/o source code'
SELECT * FROM #tmpResponse
END  --IF @debugging

END  --IF @useImplicit = 1

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#bySourceCode'))
   DROP TABLE #bySourceCode

CREATE TABLE #bySourceCode(
    SourceCode    nvarchar(60),
    TotalRevenue    decimal(18,4),
    FirstResponse    datetime,
    LastResponse    datetime,
    LowResponse    decimal(18,4),
    HighResponse    decimal(18,4),
    TotalResponses    int)

-- Aggregate the responses by source code
INSERT #bySourceCode
SELECT SourceCode, SUM(TotalRevenue), MIN(OrderDate), MAX(OrderDate), MIN(TotalRevenue),
       MAX(TotalRevenue), COUNT(*)
  FROM #tmpResponse
GROUP BY SourceCode

IF @debugging = 1
BEGIN
PRINT '#bySourceCode table after insert'
SELECT * FROM #bySourceCode
END  --IF @debugging

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#byAppeal'))
   DROP TABLE #byAppeal

CREATE TABLE #byAppeal(
    CampaignKey        uniqueidentifier,
    AppealKey        uniqueidentifier,
    SolicitationKey        uniqueidentifier,
    SourceCodeKey        uniqueidentifier,
    UserKey            uniqueidentifier,
    WasSolicited        bit)

--Aggregate the responses by appeal and user
INSERT #byAppeal
SELECT DISTINCT c.CampaignKey, a.AppealKey, s.SolicitationKey, sc.SourceCodeKey,  tr.UserKey, tr.WasSolicited
  FROM #tmpResponse tr
INNER JOIN SourceCode sc ON tr.SourceCode = sc.Code
INNER JOIN SolicitationMain s ON sc.SolicitationKey = s.SolicitationKey
INNER JOIN AppealMain a ON s.AppealKey = a.AppealKey
INNER JOIN CampaignMain c ON a.CampaignKey = c.CampaignKey

IF @debugging = 1
BEGIN
PRINT '#byAppeal table after insert'
SELECT * FROM #byAppeal
END  --IF @debugging

--Allow only one record per Appeal and User combination
SET rowcount 1

DECLARE @appealKey         uniqueidentifier
DECLARE @campaignKey         uniqueidentifier
DECLARE @respondentUserKey     uniqueidentifier

SELECT @appealKey = AppealKey, @campaignKey = CampaignKey, @respondentUserKey = UserKey
  FROM #byAppeal
GROUP BY CampaignKey, AppealKey, UserKey
HAVING COUNT(*) > 1

WHILE @@ROWCOUNT > 0
BEGIN
   DELETE #byAppeal
    WHERE AppealKey = @appealKey
      AND CampaignKey = @campaignKey
      AND UserKey = @respondentUserKey
   SELECT @appealKey = AppealKey, @campaignKey = CampaignKey, @respondentUserKey = UserKey
     FROM #byAppeal
    GROUP BY CampaignKey, AppealKey, UserKey
   HAVING COUNT(*) > 1
END  -- WHILE @@ROWCOUNT > 0

SET rowcount 0

--Update SourceCode
UPDATE sc
   SET TotalRevenue = sc.TotalRevenue + bsc.TotalRevenue,
       FirstResponseDate = CASE
                WHEN sc.FirstResponseDate IS NULL THEN bsc.FirstResponse
                WHEN bsc.FirstResponse < sc.FirstResponseDate THEN bsc.FirstResponse
                ELSE sc.FirstResponseDate END,
       LastResponseDate = CASE
                WHEN sc.LastResponseDate IS NULL THEN bsc.LastResponse
                WHEN bsc.LastResponse > sc.LastResponseDate THEN bsc.LastResponse
                ELSE sc.LastResponseDate END,
       LowResponseAmount = CASE
                WHEN sc.LowResponseAmount < 0 THEN bsc.LowResponse
                WHEN bsc.LowResponse < sc.LowResponseAmount THEN bsc.LowResponse
                ELSE sc.LowResponseAmount END,
       HighResponseAmount = CASE
                WHEN bsc.HighResponse > sc.HighResponseAmount THEN bsc.HighResponse
                ELSE sc.HighResponseAmount END,
       TotalPositiveResponse = sc.TotalPositiveResponse + bsc.TotalResponses,
       ResponsesTotalledOn = @now,
       UpdatedOn = @now,
       UpdatedByUserKey = @userKey
  FROM SourceCode sc
INNER JOIN #bySourceCode bsc ON sc.Code = bsc.SourceCode

IF @debugging = 1
BEGIN
PRINT 'SourceCode table after update'
SELECT * FROM SourceCode
END  --IF @debugging

--Create campaign opt-in
INSERT CampaignParticipation(CampaignParticipationKey,
       CampaignKey, RespondentUserKey, ResponseTypeCode, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
SELECT newid(), CampaignKey, UserKey, 1, @userKey, @now, @userKey,
       @now -- UnsolicitedResponse = Opt-In = ResponseStatus of one (1)
  FROM #byAppeal ba
WHERE NOT EXISTS(SELECT 1
                    FROM CampaignParticipation
                   WHERE CampaignKey = ba.CampaignKey
             AND RespondentUserKey = ba.UserKey)

IF @debugging = 1
BEGIN
PRINT 'CampaignParticipation table after insert'
SELECT * FROM CampaignParticipation
END  --IF @debugging

--Update appeal participation
UPDATE ap
   SET SolicitationKey = ba.SolicitationKey,
       SourceCodeKey = ba.SourceCodeKey,
       ResponseTypeCode = CASE WHEN WasSolicited = 1 THEN 0 ELSE 1 END,
       UpdatedOn = @now,
       UpdatedByUserKey = @userKey
  FROM AppealParticipation ap
INNER JOIN #byAppeal ba ON ap.AppealKey = ba.AppealKey AND ap.RespondentUserKey = ba.UserKey

IF @debugging = 1
BEGIN
PRINT 'AppealParticipation table after update'
SELECT * FROM AppealParticipation
END  --IF @debugging

--Create appeal participation
INSERT AppealParticipation
       (AppealParticipationKey, AppealKey, RespondentUserKey, SolicitationKey, SourceCodeKey,
       ResponseTypeCode, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
SELECT newid(), AppealKey, UserKey, SolicitationKey, SourceCodeKey,
       CASE WHEN WasSolicited = 1 THEN 0 ELSE 1 END,
       @userKey, @now, @userKey,
       @now --SolicitedResponse = Responded = ResponseStatus of zero (0)
  FROM #byAppeal ba
WHERE NOT EXISTS(SELECT 1
                    FROM AppealParticipation
                   WHERE AppealKey = ba.AppealKey
                     AND RespondentUserKey = ba.UserKey)

IF @debugging = 1
BEGIN
PRINT 'AppealParicipation table after insert'
SELECT * FROM AppealParticipation
END  --IF @debugging

COMMIT TRANSACTION

-- Cleanup temp tables
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#byAppeal'))
   DROP TABLE #byAppeal
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#bySourceCode'))
   DROP TABLE #bySourceCode
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'tempdb..#tmpResponse'))
   DROP TABLE #tmpResponse

-- Set the SystemConfig param to indicate this sproc has completed running
UPDATE    SystemConfig
   SET    ParameterValue = 'False'
WHERE    ParameterName = @systemConfigParamName

*/

GO